USE [ATAChietkhau]
GO

/****** Object:  StoredProcedure [dbo].[sprTheoDoiChuongTrinhXeReport]    Script Date: 07/25/2010 18:39:09 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		PhuongNT
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sprTheoDoiChuongTrinhXeReport]
	-- Add the parameters for the stored procedure here
	@BranchID UNIQUEIDENTIFIER,
	@Month INT,
	@Year INT

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @ChuongTrinhID INT
	DECLARE @BrandID INT
	DECLARE @TrungTamName NVARCHAR(255)
	DECLARE @TrungTamCode NVARCHAR(20)

	SELECT @TrungTamName = BranchName, @TrungTamCode = BranchCode FROM vwMisaBranches WHERE BranchID = @BranchID

	DECLARE @vReportDate DATETIME
	SELECT @vReportDate = dbo.Date(@Year, @Month, 1)

	DECLARE @BrandCode NVARCHAR(20)
	SELECT @BrandCode = BrandCode FROM ThuongHieu WHERE BrandID = @BrandID

    -- Insert statements for procedure here
	CREATE TABLE #tblTheoDoiChuongTrinhXeReport
	(
		TenTrungTam NVARCHAR(255),
		KhachHangID UNIQUEIDENTIFIER,
		MaKH NVARCHAR(100),
		TenKH NVARCHAR(100),
		LEVEL INT,
		ParentKhachHangID UNIQUEIDENTIFIER,
		TenChuongTrinh NVARCHAR(200),
		Thang INT,
		Nam INT,
		MucChietKhauTD DECIMAL,
		MucChietKhauCC DECIMAL,
		TenThuongHieu NVARCHAR(10),
		DuThuongID UNIQUEIDENTIFIER,
		StartDate DATETIME,
		DoanhSoCamKet MONEY,
		MaThuongHieuDangKy NVARCHAR(20),
		TenThuongHieuDangKy NVARCHAR(200)
		--DoanhSoJUT MONEY,
		--DoanhSoHIL MONEY,
		--DoanhSoFAL MONEY,
		--DoanhSoVIE MONEY,
		--DoanhSoJAJ MONEY,
		--DoanhSoATA MONEY,
		--TongDoanhSo MONEY
	)


	INSERT INTO #tblTheoDoiChuongTrinhXeReport(
	TenTrungTam, 
	KhachHangID, 
	MaKH, 
	TenKH, 
	LEVEL, 
	ParentKhachHangID, 
	TenChuongTrinh, 
	Thang, 
	Nam, 
	MaThuongHieuDangKy, 
	TenThuongHieuDangKy,
	DuThuongID, 
	StartDate, 
	MucChietKhauTD, 
	MucChietKhauCC)
	SELECT 
	@TrungTamName AS TenTrungTam, 
	D.KhachHangID, 
	D.MaKH, 
	D.TenKH, 
	D.Level, 
	D.ParentID AS ParentKhachHangID, 
	A.TenChuongTrinh, 
	@Month AS Thang, 
	@Year AS Nam, 
	MaThuongHieuDangKy,
	TenThuongHieuDangKy,
	DuThuongID, A.StartDate, A.MucChietKhauTD, A.MucChietKhauCC
	FROM vwAllCustomers D 
	JOIN (
	SELECT A.HoiVienID, C.TenChuongTrinh, B.BrandName AS TenThuongHieuDangKy, B.BrandCode AS MaThuongHieuDangKy, 
	A.ID AS DuThuongID, A.StartDate, A.MucChietKhauTD, A.MucChietKhauCC 
	FROM KhachHangDuthuong A, ThuongHieu B, ChuongTrinh C
	WHERE A.BrandID = B.BrandID 
	AND A.ChuongTrinhID = C.ChuongTrinhID
	AND A.StartDate <= @vReportDate
	AND DATEADD(mm, A.Duration, A.StartDate) > @vReportDate
	) A ON A.HoiVienID = D.KhachHangID
	WHERE 
	(D.BranchID = @BranchID OR D.BranchCode LIKE '%' + @TrungTamCode + '%')

	-- AND A.ChuongTrinhID = @ChuongTrinhID
	-- AND BrandCode = @BrandCode

	-- Lay doanh so cam ket
	DECLARE @vDoanhSoCamket MONEY
	DECLARE @vDuration INT

	DECLARE @khachHangDuThuongID UNIQUEIDENTIFIER
	DECLARE @startDate DATETIME
	DECLARE crsKH CURSOR FOR SELECT DuThuongID, StartDate FROM #tblTheoDoiChuongTrinhXeReport
	OPEN crsKH
	FETCH NEXT FROM crsKH
	INTO @khachHangDuThuongID, @startDate

	WHILE @@FETCH_STATUS = 0
	BEGIN

		SELECT @vDuration = DATEDIFF(mm, @startDate, @vReportDate) + 1
		SELECT @vDoanhSoCamket = Doanhso 
		FROM dbo.DoanhSoCamKet
		WHERE KhachHangDuThuongID = @khachHangDuThuongID
		AND MONTH = @vDuration

		UPDATE #tblTheoDoiChuongTrinhXeReport SET DoanhSoCamKet = @vDoanhSoCamKet
		WHERE DuThuongID = @khachHangDuThuongID

		FETCH NEXT FROM crsKH
		INTO @khachHangDuThuongID, @startDate
	END

	CLOSE crsKH;
	DEALLOCATE crsKH;
	-- Lay doanh so theo thuong hieu
	
	SELECT khdt.*, DoanhSo.ThuongHieu, DoanhSo.DoanhSo,
	DoanhSoTD, DoanhSoCC
	FROM #tblTheoDoiChuongTrinhXeReport khdt
	LEFT JOIN (
	SELECT ds.KhachHangID, ThuongHieu, SUM(ISNULL(ds.DoanhSoTD, 0) + ISNULL(ds.DoanhSoCC, 0)) AS DoanhSo,
	SUM(ISNULL(ds.DoanhSoTD, 0)) AS DoanhSoTD, SUM(ISNULL(ds.DoanhSoCC, 0)) AS DoanhSoCC
	FROM vwGetTotalMonthlyRevenue ds
	WHERE 
	ds.Thang = @Month AND ds.Nam = @Year
	GROUP BY ds.KhachHangID, ds.ThuongHieu) DoanhSo ON khdt.KhachHangID = DoanhSo.KhachHangID

	
	--SELECT khdt.*, 
	--DoanhSoATA.DoanhSoATA, 
	--DoanhSoPalis.DoanhSoPalis, 
	--DoanhSoHT.DoanhSoHT, 
	--DoanhSoJAJ.DoanhSoJAJ, 
	--DoanhSoJUN.DoanhSoJuntuner, 
	--DoanhSoTong.DoanhSoTong, 
	--DoanhSoVW.DoanhSoVW,
	--DoanhSoTong.DoanhSoTong - DoanhSoVW.DoanhSoVW - DoanhSoATA.DoanhSoATA - DoanhSoPalis.DoanhSoPalis - DoanhSoHT.DoanhSoHT - DoanhSoJAJ.DoanhSoJAJ - DoanhSoJUN.DoanhSoJuntuner AS DoanhSoKhac
	--FROM #tblTheoDoiChuongTrinhXeReport khdt
	--LEFT JOIN (
	--SELECT ds.AccountingObjectID, SUM(ds.DoanhSoTrongThang) AS DoanhSoJuntuner 
	--FROM vwGetAllDoanhSo ds, #tblTheoDoiChuongTrinhXeReport kh
	--WHERE ds.PhanLoaiHang LIKE + 'JUN-%'
	--AND MONTH(ds.NgayHoaDon) = @Month AND YEAR(ds.NgayHoaDon) = @Year
	--GROUP BY ds.AccountingObjectID) DoanhSoJUN ON khdt.KhachHangID = DoanhSoJUN.AccountingObjectID
	--LEFT JOIN (
	--SELECT ds.AccountingObjectID, SUM(ds.DoanhSoTrongThang) AS DoanhSoHT
	--FROM vwGetAllDoanhSo ds, #tblTheoDoiChuongTrinhXeReport kh
	--WHERE ds.PhanLoaiHang LIKE + 'HT-%'
	--AND MONTH(ds.NgayHoaDon) = @Month AND YEAR(ds.NgayHoaDon) = @Year
	--GROUP BY ds.AccountingObjectID) DoanhSoHT ON khdt.KhachHangID = DoanhSoHT.AccountingObjectID
	--LEFT JOIN (
	--SELECT ds.AccountingObjectID, SUM(ds.DoanhSoTrongThang) AS DoanhSoPalis
	--FROM vwGetAllDoanhSo ds, #tblTheoDoiChuongTrinhXeReport kh
	--WHERE ds.PhanLoaiHang LIKE + 'PL-%'
	--AND MONTH(ds.NgayHoaDon) = @Month AND YEAR(ds.NgayHoaDon) = @Year
	--GROUP BY ds.AccountingObjectID) DoanhSoPalis ON khdt.KhachHangID = DoanhSoPalis.AccountingObjectID
	--LEFT JOIN (
	--SELECT ds.AccountingObjectID, SUM(ds.DoanhSoTrongThang) AS DoanhSoVW
	--FROM vwGetAllDoanhSo ds, #tblTheoDoiChuongTrinhXeReport kh
	--WHERE ds.PhanLoaiHang LIKE + 'VW-%'
	--AND MONTH(ds.NgayHoaDon) = @Month AND YEAR(ds.NgayHoaDon) = @Year
	--GROUP BY ds.AccountingObjectID) DoanhSoVW ON khdt.KhachHangID = DoanhSoVW.AccountingObjectID
	--LEFT JOIN (
	--SELECT ds.AccountingObjectID, SUM(ds.DoanhSoTrongThang) AS DoanhSoJAJ
	--FROM vwGetAllDoanhSo ds, #tblTheoDoiChuongTrinhXeReport kh
	--WHERE ds.PhanLoaiHang LIKE + 'JAJ-%'
	--AND MONTH(ds.NgayHoaDon) = @Month AND YEAR(ds.NgayHoaDon) = @Year
	--GROUP BY ds.AccountingObjectID) DoanhSoJAJ ON khdt.KhachHangID = DoanhSoJAJ.AccountingObjectID
	--LEFT JOIN (
	--SELECT ds.AccountingObjectID, SUM(ds.DoanhSoTrongThang) AS DoanhSoATA
	--FROM vwGetAllDoanhSo ds, #tblTheoDoiChuongTrinhXeReport kh
	--WHERE ds.PhanLoaiHang LIKE + 'ATA-%'
	--AND MONTH(ds.NgayHoaDon) = @Month AND YEAR(ds.NgayHoaDon) = @Year
	--GROUP BY ds.AccountingObjectID) DoanhSoATA ON khdt.KhachHangID = DoanhSoATA.AccountingObjectID
	--LEFT JOIN (
	--SELECT ds.AccountingObjectID, SUM(ds.DoanhSoTrongThang) AS DoanhSoTong
	--FROM vwGetAllDoanhSo ds, #tblTheoDoiChuongTrinhXeReport kh
	--WHERE MONTH(ds.NgayHoaDon) = @Month AND YEAR(ds.NgayHoaDon) = @Year
	--GROUP BY ds.AccountingObjectID) DoanhSoTong ON khdt.KhachHangID = DoanhSoTong.AccountingObjectID
	--ORDER BY ParentKhachHangID, Level

	DROP TABLE #tblTheoDoiChuongTrinhXeReport

	-- exec sprTheoDoiChuongTrinhXeReport '0A110906-E0A3-46AB-B716-4F56085945D5', 6, 2010
END

GO


